#' load data from cityRE database.
#'
#' This function load data from cityRE database.
#' @param city_code character.the code of city
#' @param ha_cl_type character.the name of ha type. '楼盘小区'|'写字楼'
#' @param con connection. Mysql Database connection object.If missing ,a connection with default parameter will be contrustruted automatically and will be disconnected when finished.
#' @param apply.patch logic indicating whether to apply data patch including coordinates correction and hh routes inserting.
#' @export
#' @return a list object contains all datatable
#' @details the returned list object contains three kinds of data.The first type of data is table in the database (ha_bldg,ha_gps,ha_hushu,ha_info,ha_phase,ha_poi,ha_poi_gps,ha_price,routes).The second is spatial data of ha and poi data(ha_info.sp and ha_poi.sp).The last catagory of data is spatial POI data. Right now we have develop four type of POI including edu.poi.sp,hosi.poi.sp,trans.poi.sp and busi.poi.sp.
#' @details If city has no routes, the returned list object will not contain trans.poi.sp.
#' @examples
#' ######load all house data in qingdao###
#' data.list<-loadData(city_code='qd')
loadData<-function(city_code='bj',ha_cl_type='楼盘小区',con=NULL,
                   apply.patch=T){
  # if(is.null(con)){
  #   stop('you must provide con object!!')
  # }
  needclose<-F
  if(is.null(con)){
    warning('trying connect database with default parameters because con is null!')
    con<-connectDB()
    needclose<-T
  }
  if(apply.patch){
    apply.data.patch(con=con)
  }
  count_sql<-'select * from '
  tables.names<-c('ha_bldg','ha_gps','ha_hushu','ha_info','ha_phase',
                  'ha_poi','ha_poi_gps','ha_price','routes')
  tabln.vec<-vector(mode='list',length(tables.names))
  names(tabln.vec)<-tables.names
  tabln.cache<-paste0(city_code,'_','tabln_vec.rds')
  dir.create('cache')
  tabln.cache<-file.path('cache',tabln.cache)
  if(file.exists(tabln.cache)){

    tabln.vec<-readRDS(tabln.cache)
    cat('cache file is loading.....','\n')
  }else{
    for (ii in 1:length(tables.names)){
      tbn<-tables.names[ii]
      cat('loading for ',city_code,'_',tbn,'\n')
      sqlcmd<-paste0(count_sql,tbn," where city_code=\'"
                     ,city_code,"\' ")
      tabln.vec[[ii]]<-dbGetQuery(con,sqlcmd)
    }
    saveRDS(tabln.vec,tabln.cache)
  }
  library(dplyr)
  tabln.vec$ha_price<-tabln.vec$ha_price%>%mutate(
    year=ifelse(ymid%%12,floor(ymid/12),floor(ymid/12)-1),
    month=ifelse(ymid%%12,ymid-floor(ymid/12)*12,12))

  # To simplified handling with data, we can compute mean value of coordinates.
  tabln.vec$ha_gps<-tabln.vec$ha_gps%>%
    filter(!is.na(x)&!is.na(y))%>%
    group_by(ha_code)%>%
    summarise(x=mean(x,trim=0.2),y=mean(y,trim=0.2))%>%as.data.frame
  # To simplified handling with buildyear,we can compute mean value of buildyear.
  tabln.vec$ha_phase<-tabln.vec$ha_phase%>%
    filter(!is.na(buildyear))%>%
    group_by(ha_code)%>%
    summarise(buildyear=round(mean(buildyear,trim=0.2)))%>%as.data.frame

  #  notice: some ha have no coordinates
  if(is.null(ha_cl_type)|ha_cl_type==''|
     (ha_cl_type!='楼盘小区'&ha_cl_type!='写字楼')){

    tabln.vec$ha_info.sp<-merge(tabln.vec$ha_info,
                                tabln.vec$ha_gps,by='ha_code')
  }else{
    tabln.vec$ha_info.sp<-merge(tabln.vec$ha_info,
                                tabln.vec$ha_gps,by='ha_code')%>%
      filter(ha_cl_name==ha_cl_type)
  }
  tabln.vec$ha_info.sp<-merge(tabln.vec$ha_info.sp,tabln.vec$ha_phase,
                              by='ha_code')
  library(maptools)
  tabln.vec$ha_info.sp<-tabln.vec$ha_info.sp[
    which.in.china(cbind(tabln.vec$ha_info.sp$x,tabln.vec$ha_info.sp$y)),]

  coordinates(tabln.vec$ha_info.sp)<-~x+y
  proj4string(tabln.vec$ha_info.sp)<-'+init=epsg:4326'
  # notice : like ha coordinates issue , just compute mean value .
  cat('created ha_poi_gps','\n')
  tabln.vec$ha_poi.sp<-tabln.vec$ha_poi_gps%>%
    filter(!is.na(x)&!is.na(y))%>%
    group_by(ha_code)%>%
    summarise(x=mean(x),y=mean(y))%>%as.data.frame
  #
  tabln.vec$ha_poi.sp<-merge(tabln.vec$ha_poi,
                             tabln.vec$ha_poi.sp,by='ha_code')
  tabln.vec$ha_poi.sp<-tabln.vec$ha_poi.sp[
    which.in.china(cbind(tabln.vec$ha_poi.sp$x,tabln.vec$ha_poi.sp$y)),]
  coordinates(tabln.vec$ha_poi.sp)<-~x+y
  proj4string(tabln.vec$ha_poi.sp)<-'+init=epsg:4326'

  cat('created edu.poi.sp','\n')
  tabln.vec$edu.poi.sp<-tabln.vec$ha_poi.sp[
    tabln.vec$ha_poi.sp$ha_cl_name=='教育'&
      (tabln.vec$ha_poi.sp$ha_cl_name_child=='中学'|
         tabln.vec$ha_poi.sp$ha_cl_name_child=='小学'|
         tabln.vec$ha_poi.sp$ha_cl_name_child=='幼儿园'),]
  # hosipital POI
  # check categories of POI
  # unique(ha_poi.sp[ha_poi.sp$ha_cl_name=='医疗',]$ha_cl_name_child)
  cat('created hosipital POI','\n')
  tabln.vec$hosi.poi.sp<-
    tabln.vec$ha_poi.sp[tabln.vec$ha_poi.sp$ha_cl_name_child=='医院',]
  #### trouble : 没有三甲医院
  ## transportation POI
  cat('created transportation POI','\n')
  if(nrow(tabln.vec$routes)>0){
    tabln.vec$trans.poi.sp<-tabln.vec$routes%>%
      filter(!is.na(longitude)&!is.na(latitude))
    coordinates(tabln.vec$trans.poi.sp)<-~longitude+latitude
    proj4string(tabln.vec$trans.poi.sp)<-'+init=epsg:4326'
  }else{
    warning(paste(city_code,'have no routes!!!'))
    # tabln.vec$trans.poi.sp<-NULL
  }

  ### business POI
  cat('created business POI','\n')
  tabln.vec$busi.poi.sp<-
    tabln.vec$ha_poi.sp[
      tabln.vec$ha_poi.sp$ha_cl_name_child=='商圈'|
        tabln.vec$ha_poi.sp$ha_cl_name_child=='商业街'|
        tabln.vec$ha_poi.sp$ha_cl_name_child=='商场'|
        tabln.vec$ha_poi.sp$ha_cl_name_child=='市场',]
  if(needclose) dbDisconnect(con)
  tabln.vec
}
outofChina <- function(lat, lon){
  if(lon < 72.004 | lon > 137.8347) return(TRUE)
  if(lat < 0.8293 | lat > 55.8271) return(TRUE)
  return(FALSE)
}
which.in.china<-function(x){
  coords.xy<-coordinates(x)
  sapply(1:nrow(x), function(i){
    return (!outofChina(coords.xy[i,1],lat=coords.xy[i,2]))
  })
}
apply.data.patch<-function(con=NULL){
  sql_updatedata<-paste(
    'update ha_gps set x=117.211631040827,y=32.9816305610632 where ha_code=0805225593',
    'update ha_gps set x=112.940565544669,y=27.1033991989137 where ha_code=\'pa0002778HD926\'',
    'update ha_gps set x=108.368199272858,y=22.7783196353541 where ha_code=\'pa0036368LQ849\'',
    'update ha_gps set x=117.199435472451,y=39.1286833040535 where ha_code=7722992774',sep=';')
  library(stringr)
  sql_updatedata<-str_split(sql_updatedata,';')%>%unlist
  for(i in 1:length(sql_updatedata)){
    # cat(sql_updatedata[i],'\n')
    dbSendQuery(con,sql_updatedata[i])
  }
  dbSendQuery(con,'delete from routes where city_code=\'hh\'')
  dbWriteTable(con, value =hh.routes, name = "routes", append = T,row.names = F)
}
